/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package monitoring.autocomplete;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;
import javax.el.ELContext;
import javax.faces.bean.ManagedBean;
import javax.faces.bean.RequestScoped;
import javax.faces.component.UIComponent;
import javax.faces.context.FacesContext;
import javax.faces.convert.Converter;
import javax.faces.convert.FacesConverter;
import system.general.Exc;
import system.general.Logged;
import system.general.Persistencia;

/**
 *
 * @author emojica
 */
@ManagedBean(name = "monitoring_autoCompleteFind")
@FacesConverter(value = "monitoring_autoCompleteFind")
@RequestScoped
public class Find implements Converter {

    Logged logged;
    private Persistencia persistencia = new Persistencia();
    private Beans selectedBean = new Beans();
    private String sql = "";
//    private String command = "SELECT  u.id as u_id, u.nombre as u_nombre, u.apellido as u_apellido, u.usuario as u_usuario, u.clave as u_clave, u.id_compania as u_id_compania, "
//            + "u.idl_rol as u_rol, u.unlogin as u_unlogin, u.login as u_login, u.registrado as u_registrado, u.activo as u_activo, "
//            + "gps.id as gps_id, gps.imei as gps_imei, gps.numero_serie as gps_numero_serie, gps.marca as gps_marca, gps.modelo as gps_modelo, "
//            + "gps.nombre as gps_nombre, gps.tipo as gps_tipo, gps.detalle as gps_detalle, gps.caducidad as gps_caducidad "
//            + "FROM public.v_usuarios u "
//            + "LEFT OUTER JOIN public.v_usuario_gps ugps ON (ugps.id_usuario= u.id) "
//            + "LEFT OUTER JOIN public.v_gps gps ON (gps.id= ugps.id_gps) WHERE ";
    private String command = "SELECT u.nombre, u.apellido, gps.id as gps_id, gt.imei, gps.modelo, gps.marca, gps.numero_serie"
            + " FROM public.v_gps_tramas gt "
            + " INNER JOIN public.v_usuarios u on (u.id = gt.id_usuario) "
            + " INNER JOIN public.v_gps gps on (gps.imei = gt.imei) WHERE ";
    private String order = "GROUP BY gt.imei, u.nombre, u.apellido, gps.modelo, gps.marca, gps.numero_serie, gps.id ORDER BY u.nombre, gt.imei";

    public Find() {
        ELContext elContext = FacesContext.getCurrentInstance().getELContext();
        logged = (Logged) FacesContext.getCurrentInstance().getApplication().getELResolver().getValue(elContext, null, "logged");
    }

    public Beans getSelected() {
        return selectedBean;
    }

    public void setSelected(Beans bean) {
        if (bean != null) {
            logged.setMasterId(selectedBean.getGpsBeans().getId());
        }
    }

    public boolean byExist(Map map) throws Exc, SQLException, Exception {
        List<Beans> listBean = byMap(map);
        if (listBean.isEmpty()) {
            listBean.clear();
            return false;
        }
        listBean.clear();
        return true;
    }

    public List<Beans> autoComplete(String data) throws Exc, SQLException, Exception {
        data = data.replace(" ", "|");
        Map<String, String> map = new LinkedHashMap<>();
        if (logged.getLoggedRol() == 2) {
            map.put("u.id_compania= (SELECT id_compania FROM public.v_usuarios u WHERE u.id=" + logged.getLoggedId() + ")", " AND ");
        } else if (logged.getLoggedRol() == 3) {
            map.put("gt.id_usuario= " + logged.getLoggedId(), " AND ");
        }

        map.put("(lower(gps.imei) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(gps.numero_serie) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(gps.marca) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(gps.modelo) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(u.nombre) SIMILAR TO '%(" + strChar(data) + ")%'", " OR ");
        map.put("lower(u.apellido) SIMILAR TO '%(" + strChar(data) + ")%'", ") ");


        return byMap(map);
    }

    public List<Beans> byMap(Map map) throws Exc, SQLException, Exception {
        Iterator it = map.entrySet().iterator();
        StringBuilder criteria = new StringBuilder("");
        while (it.hasNext()) {
            Map.Entry e = (Map.Entry) it.next();
            criteria.append(e.getKey().toString());
            criteria.append(e.getValue().toString());
        }
        sql = command + criteria + order;
        return executeSql(sql);
    }

    private List<Beans> executeSql(String sql) throws Exc, SQLException, Exception {
        List<Beans> listBean = new ArrayList<>();
        ResultSet rsts = persistencia.executeRsts(sql);
        try {
            while (rsts.next()) {
                Beans beans = new Beans(new gps.general.Beans(), new user.login.Beans());
                beans.getUserBeans().setNombre(isNull(rsts.getString("nombre")));
                beans.getUserBeans().setApellido(isNull(rsts.getString("apellido")));
                beans.getGpsBeans().setId(rsts.getInt("gps_id"));
                beans.getGpsBeans().setImei(isNull(rsts.getString("imei")));
                beans.getGpsBeans().setMarca(isNull(rsts.getString("marca")));
                beans.getGpsBeans().setModelo(isNull(rsts.getString("modelo")));
                beans.getGpsBeans().setNumero_serie(isNull(rsts.getString("numero_serie")));
                listBean.add(beans);
            }
        } catch (Exception ex) {
            System.out.println(ex);
        }
        //Desconecta de la base de datos debido a que es un resultset y podria quedar la conexion activa
        persistencia.dbDisconnect();
        return listBean;
    }

    private String isNull(String cadena) {
        if (cadena == null) {
            return "";
        }
        return cadena;
    }

    private String strChar(String data) {
        data = data.toLowerCase().trim();
        data = data.replaceAll("á", "a");
        data = data.replaceAll("é", "e");
        data = data.replaceAll("í", "i");
        data = data.replaceAll("ó", "o");
        data = data.replaceAll("ú", "u");
        data = data.replaceAll("a", "[a,á]");
        data = data.replaceAll("e", "[e,é]");
        data = data.replaceAll("i", "[i,í]");
        data = data.replaceAll("o", "[o,ó]");
        data = data.replaceAll("u", "[u,ú]");
        return data;
    }

    @Override
    public Object getAsObject(FacesContext facesContext, UIComponent component, String submittedValue) {
        if (submittedValue.trim().equals("0")) {
            return null;
        }
        int id = Integer.parseInt(submittedValue.substring(0, submittedValue.indexOf("-")));
        String name = submittedValue.substring(submittedValue.indexOf("-") + 1, submittedValue.length());
        logged.setMasterId(id);
        logged.setMasterName(name);
        return null;
    }

    @Override
    public String getAsString(FacesContext facesContext, UIComponent component, Object value) {
        if (((Beans) value).getGpsBeans().getId() == 0) {
            return "";
        }
        return String.valueOf(((Beans) value).getGpsBeans().getId()) + "-" + ((Beans) value).getGpsBeans().getNombre();
    }
}
